import random
import requests
import ddddocr
from PIL import Image
from io import BytesIO
from bs4 import BeautifulSoup
import openpyxl
import time
import os

workbookRES = None
worksheetRES = None
Log= None

user_agents = [
    "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; AcooBrowser; .NET CLR 1.1.4322; .NET CLR 2.0.50727)",
    "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; Acoo Browser; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506)",
    "Mozilla/4.0 (compatible; MSIE 7.0; AOL 9.5; AOLBuild 4337.35; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)",
    "Mozilla/5.0 (Windows; U; MSIE 9.0; Windows NT 9.0; en-US)",
    "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Win64; x64; Trident/5.0; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 2.0.50727; Media Center PC 6.0)",
    "Mozilla/5.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 1.0.3705; .NET CLR 1.1.4322)",
    "Mozilla/4.0 (compatible; MSIE 7.0b; Windows NT 5.2; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30)",
    "Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN) AppleWebKit/523.15 (KHTML, like Gecko, Safari/419.3) Arora/0.3 (Change: 287 c9dfb30)",
    "Mozilla/5.0 (X11; U; Linux; en-US) AppleWebKit/527+ (KHTML, like Gecko, Safari/419.3) Arora/0.6",
    "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.2pre) Gecko/20070215 K-Ninja/2.1.1",
    "Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.9) Gecko/20080705 Firefox/3.0 Kapiko/3.0",
    "Mozilla/5.0 (X11; Linux i686; U;) Gecko/20070322 Kazehakase/0.4.5",
    "Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.0.8) Gecko Fedora/1.9.0.8-1.fc10 Kazehakase/0.5.6",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11",
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_3) AppleWebKit/535.20 (KHTML, like Gecko) Chrome/19.0.1036.7 Safari/535.20",
    "Opera/9.80 (Macintosh; Intel Mac OS X 10.6.8; U; fr) Presto/2.9.168 Version/11.52",
]
request_headers = {
    'user-agent': random.choice(user_agents),
    'Host': 'zkzy.hhkszx.cn',
    'Origin': 'https://zkzy.hhkszx.cn',
    'Referer': 'https://zkzy.hhkszx.cn/zkbmcx/chengji2023.jsp'
}
cookie = {}
url = 'https://zkzy.hhkszx.cn/zkbmcx/chengji2023.jsp'

def init():
    # 创建一个新的 Excel 工作簿
    global workbookRES
    global worksheetRES
    global Log
    workbookRES = openpyxl.Workbook()

    # 选择默认的活动工作表
    worksheetRES = workbookRES.active
    worksheetRES.title= '查询结果'
    Log = workbookRES.create_sheet(title='错误日志')

    # 设置列宽
    Log.column_dimensions['B'].width= 20

    # 合并A1和B1单元格
    Log.merge_cells('A1:B1')
    # 设置合并后的单元格的值
    Log['A1'] = '错误日志'
    Log['A2'] = '准考证号'
    Log['B2'] = '身份证号'
    worksheetRES['A1'] = '准考证号'
    worksheetRES['B1'] = '考生号'
    worksheetRES['C1'] = '姓名'
    worksheetRES['D1'] = '语文'
    worksheetRES['E1'] = '数学'
    worksheetRES['F1'] = '英语'
    worksheetRES['G1'] = '化学'
    worksheetRES['H1'] = '物理'
    worksheetRES['I1'] = '历史'
    worksheetRES['J1'] = '政治'
    worksheetRES['K1'] = '体育'
    worksheetRES['L1'] = '总分'

def getCookie():
    res = requests.get(url=url, headers=request_headers)
    cookie['JSESSIONID'] = res.cookies['JSESSIONID']  # 获取cookie


def getVerifyCode():
    url = 'https://zkzy.hhkszx.cn/zkbmcx/VerifyCode?r=' + str(random.random())
    res = requests.get(url=url, headers=request_headers, cookies=cookie)
    image = Image.open(BytesIO(res.content))
    # Image._show(image)
    return image


def regVerifyCode(img):
    ocr = ddddocr.DdddOcr()
    # print(ocr.classification(img))
    code = ocr.classification(img)
    # code= input('验证码:')
    return code


def getGrade(stuInfo, pvcode):
    data = {
        'examcode': stuInfo['准考证'],
        'exaidnum': stuInfo['身份证号'],
        'pvcode': pvcode
    }
    res = requests.post(url=url, data=data, headers=request_headers, cookies=cookie, timeout=5)
    soup = BeautifulSoup(res.content, "html.parser")
    global worksheetRES
    global Log
    if soup.select('table:nth-of-type(2) tr td')[0].text == '验证码输入错误！':
        again(stuInfo)
    else:
        # 写入数据到单元格
        try:
            td_elements = soup.find_all('table')[1].find_all('tr')[3].find_all('td')
        except IndexError:
            errinfo = [stuInfo['准考证'], stuInfo['身份证号']]
            Log.append(errinfo)
            return
        grade = [td.text for td in td_elements]
        worksheetRES.append(grade)


def again(stuInfo):  # 登录并获取数据
    # print('data:', stuInfo)
    img = getVerifyCode()
    code = regVerifyCode(img)
    getGrade(stuInfo, code)


def main(filename):
    '''
    首先获取cookie->请求验证码->识别验证码code
    遍历data的每一行->拿到学生的准考证号和身份证号stuInfo

    使用stuInfo和验证码code进行登录:
        可能会连接错误，过会重试
        可能会验证码识别错误，需要重新获取验证码进行识别
    :param filename: 用户上传的学生信息
    :return:
    '''
    init()
    getCookie()
    workbook = openpyxl.load_workbook(filename=filename, data_only=False)  # 读取文件

    # 选择默认的活动工作表
    worksheet = workbook.active

    # 获取表头（第一行）的值
    header_list = [cell.value for cell in worksheet[1]]
    target_columns = ['准考证', '身份证号']
    column_indices = [index for index, header in enumerate(header_list) if
                      header in target_columns]  # 获取['准考证', '身份证号']对应的列号

    # 遍历行
    for row in worksheet.iter_rows(min_row=2):  # 设置起始行为2
        # 遍历指定的列索引
        stuInfo = {header_list[idx]: str(row[idx].value) for idx in column_indices}

        try:
            again(stuInfo)  # data格式为{'准考证':'XXX','身份证号':'XXX'}, 写入成绩
        except requests.exceptions.ConnectTimeout:
            # 网络不好，过5秒再试
            time.sleep(5)
            again(stuInfo)

    # 保存工作簿到文件
    global workbookRES
    workbookRES.save('output.xlsx')
    workbookRES.close()

